﻿using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Springwater.Business.Excel
{
    public class ExcelDataReader
    {
        public static List<Dictionary<string, string>> Read(Stream stream, List<string> errorList)
        {
            // Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            // DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            List<Dictionary<string, string>> resultDictionray = ProcessTable(result.Tables[0], errorList);

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();

            return resultDictionray;
        }

        private static List<Dictionary<string, string>> ProcessTable(DataTable table, List<string> errorList)
        {
            var headerDictionary = GetHeaderDictionary(table.Columns, errorList);
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();

            ValidateHeader(headerDictionary, errorList);

            if (errorList.Count == 0)
            {
                foreach (DataRow row in table.Rows)
                {
                    var rowDictionary = ParsRow(row, headerDictionary);
                    ValidateRow(rowDictionary, errorList);

                    if (!string.IsNullOrWhiteSpace(rowDictionary[ExcelTemplate.Column.Kod]))
                    {
                        result.Add(rowDictionary);
                    }
                }
            }

            return result;
        }

        private static Dictionary<string, string> ParsRow(DataRow row, Dictionary<string, int> headerDictionary)
        {
            Dictionary<string, string> dictionary = new Dictionary<string,string>();

            foreach (var column in headerDictionary.Keys)
            {
                string value = null;

                switch (column)
                {
                    case "Hőmérséklet":
                        value = row[headerDictionary[column]].ToString().Trim().Replace("oC", string.Empty);
                        break;
                    case "NH4 (mg/l)":
                    case "NO3 (ppm)":
                    case "Cl (mg/l)":
                    case "Mg (mg/l)":
                    case "Ca (mg/l)":
                        value = row[headerDictionary[column]].ToString().Trim().Replace("low", "0");
                        break;
                    default:
                        value = row[headerDictionary[column]].ToString().Trim();
                        break;
                }
                dictionary.Add(column, value);
            }

            return dictionary;
        }

        private static Dictionary<string, int> GetHeaderDictionary(DataColumnCollection columns, List<string> errorList)
        {
            Dictionary<string, int> dictionary = new Dictionary<string, int>();
            int index = 0;

            foreach (DataColumn column in columns)
            {

                if (!string.IsNullOrWhiteSpace(column.ColumnName))                
                {
                    if (ExcelTemplate.Column.AllCollumns.Contains(column.ColumnName))
                    {
                        dictionary.Add(column.ColumnName, index);
                    }
                    else
                    {
                        //errorList.Add(string.Format("Unidentified column : {0}", column.ColumnName));
                    }
                }               

                index++;
            }

            return dictionary;
        }

        private static void ValidateHeader(Dictionary<string, int> headerDictionary, List<string> errorList)
        {
            foreach (string column in ExcelTemplate.Column.AllCollumns)
            {
                if (!headerDictionary.Keys.Any(c => c.Equals(column)))
                {
                    errorList.Insert(0, string.Format("The header column: {0} is missing!", column));
                }
            }
        }

        private static void ValidateRow(Dictionary<string, string> dictionary, List<string> errorList)
        {
            if (!string.IsNullOrEmpty(dictionary[ExcelTemplate.Column.Kod]))
            {               
                if (string.IsNullOrEmpty(dictionary[ExcelTemplate.Column.Dátum]))
                {
                    errorList.Add(string.Format("Kod: {0} - Dátum hianyzik!", dictionary[ExcelTemplate.Column.Kod]));
                }
            }
        }
    }
}
